from tkinter.font import names
from xml.sax import parse

from PySide6.QtWidgets import QWidget,QTableWidgetItem,QHeaderView,QMenu,QAbstractItemView,QMessageBox,QFileDialog
from PySide6.QtCore import Qt
from sqlalchemy.dialects.mssql.information_schema import columns
from sqlalchemy.orm import Session
import os,shutil,pandas as pd
from settings.publicvar import engine
from views.learningexperience.learning_experience import Ui_learningexperience
from views.learningexperience.operationlearnView import OperationLeanView
from views.learningexperience.model.learningexperience import LearningExperience
from views.person.model.person import PersonBaseInfo
from settings.publicFuncation.exceldownloadimport import OperationModel


class LearningExperienceView(QWidget,Ui_learningexperience):
    def __init__(self):
        super().__init__()
        self.setupUi(self)
        self.learningexperiencetable.setEditTriggers(QAbstractItemView.NoEditTriggers)
        self.learningexperiencetable.setSelectionBehavior(QAbstractItemView.SelectRows)
        self.learningexperiencetable.horizontalHeader().setSectionResizeMode(QHeaderView.Interactive)
        self.learningexperiencetable.setContextMenuPolicy(Qt.CustomContextMenu)
        self.learningexperiencetable.customContextMenuRequested.connect(self.generate_menu)

        self.addpersonbaseinfo.clicked.connect(self.addDataFun)
        self.serearchbtn.clicked.connect(self.serearchFun)
        self.downloadmodle.clicked.connect(self.downloadmodleFun)
        self.importbtn.clicked.connect(self.importdataFun)
        self.sereachedit.editingFinished.connect(self.serearchFun)
        self.loadTableData()

    # 表结构
    def setTableStructure(self, data):
        self.learningexperiencetable.setRowCount(len(data))
        for i,item in enumerate(data):
            idx = QTableWidgetItem(str(item.id))
            self.learningexperiencetable.setItem(i,0,idx)
            try:
                name = QTableWidgetItem(item.personbase.name)
            except:
                name = QTableWidgetItem('')
            self.learningexperiencetable.setItem(i, 1, name)
            start_date = QTableWidgetItem(str(item.start_date))
            self.learningexperiencetable.setItem(i,2,start_date)
            end_date = QTableWidgetItem(str(item.end_date))
            self.learningexperiencetable.setItem(i, 3, end_date)
            school = QTableWidgetItem(item.school)
            self.learningexperiencetable.setItem(i,4,school)
            education_qualifications = QTableWidgetItem(item.education_qualifications)
            self.learningexperiencetable.setItem(i,5,education_qualifications)
            professional = QTableWidgetItem(item.professional)
            self.learningexperiencetable.setItem(i,6,professional)
            degreen = QTableWidgetItem(item.degreen)
            self.learningexperiencetable.setItem(i,7,degreen)
            degreen_name = QTableWidgetItem(item.degreen_name)
            self.learningexperiencetable.setItem(i,8,degreen_name)
            degreen_number = QTableWidgetItem(item.degreen_number)
            self.learningexperiencetable.setItem(i,9,degreen_number)
            education_mode = QTableWidgetItem(item.education_mode)
            self.learningexperiencetable.setItem(i,10,education_mode)
            school_year = QTableWidgetItem(item.school_year)
            self.learningexperiencetable.setItem(i,11,school_year)
            graduate_number = QTableWidgetItem(item.graduate_number)
            self.learningexperiencetable.setItem(i,12,graduate_number)
            witness = QTableWidgetItem(item.witness)
            self.learningexperiencetable.setItem(i,13,witness)
            mark = QTableWidgetItem(item.mark)
            self.learningexperiencetable.setItem(i,14,mark)

    # 表格数据加载
    def loadTableData(self):
        with Session(engine) as session,session.begin():
            learningexperiences = session.query(LearningExperience).all()
            self.setTableStructure(learningexperiences)

    # 增加单个数据
    def addDataFun(self):
        operation= OperationLeanView('add',False)
        operation.exec()
        self.loadTableData()

    # 菜单数据
    def generate_menu(self,pos):
        menu = QMenu()
        viewitem = menu.addAction('查看')
        changeitem = menu.addAction('修改')
        deletitem = menu.addAction('删除')
        action = menu.exec(self.learningexperiencetable.mapToGlobal(pos))
        posrow = self.learningexperiencetable.currentRow()
        columns = self.learningexperiencetable.columnCount()
        data = [self.learningexperiencetable.item(posrow, i).text() for i in range(columns)]
        if action == changeitem:
            learningexperience = OperationLeanView('change',data)
            learningexperience.exec()
            pass
        elif action == deletitem:
            learningexperience = OperationLeanView('delete',data)
            learningexperience.exec()
        elif action == viewitem:
            learningexperience = OperationLeanView('view', data)
            learningexperience.exec()
            pass
        self.loadTableData()

    # 搜索框数据处理-只获取用户数据
    def serearchFun(self):
        sereachdata = self.sereachedit.text().strip()
        if sereachdata == '':
            QMessageBox.critical(None, '错误', '搜索框不能为空！')
            self.loadTableData()
        else:
             with Session(engine) as session, session.begin():
                sereachName = session.query(PersonBaseInfo).filter(
                        PersonBaseInfo.name.like('%' + sereachdata + '%')).all()
                sereachIDcard = session.query(PersonBaseInfo).filter(
                        PersonBaseInfo.IDcard.like('%' + sereachdata + '%')).all()
                sereachiphone = session.query(PersonBaseInfo).filter(
                        PersonBaseInfo.iphone.like('%' + sereachdata + '%')).all()
                sereachData = sereachName + sereachIDcard + sereachiphone
                items = []
                if sereachData:
                    for personbase in sereachData:
                        for learningexperience in personbase.learningexperiences:
                            items.append(learningexperience)
                    self.setTableStructure(items)
                    # 数据处理和加载QTable表
                else:
                    QMessageBox.critical(None, '没有数据', '你搜索数据不存在！')

    # 下载模板
    def downloadmodleFun(self):
        OperationModel().downloadmodleFun('学习经历模板.xlsx')

    # 导入数据处理
    def importdataFun(self):
        names = ['idx','name','IDcard','start_date','end_date','school','education_qualifications','professional','degreen','degreen_name',
                 'degreen_number','education_mode','school_year','graduate_number','witness','mark']
        dtype = {'IDcard': str, 'school_year': str, 'graduate_number': str,'degreen_number':str}
        parse_dates = ['start_date', 'end_date']
        data = OperationModel().importdataFun(names,dtype,parse_dates)
        data['start_date'] = data['start_date'].replace({pd.NaT: None})
        data['end_date'] = data['end_date'].replace({pd.NaT: None})
        with Session(engine) as session,session.begin():
            exist_personbase = session.query(PersonBaseInfo).filter(
                PersonBaseInfo.IDcard.in_(data.IDcard.to_list())).all()
            # 找出存入数据tem,已经有的数据
            tem = []
            graduate_numbers = []
            for item in exist_personbase:
                person_learns= item.learningexperiences
                if not person_learns:
                    tem.append((item.id,item.IDcard))
                else:
                    graduate_numbers += [tem.graduate_number for tem in person_learns]
            if not tem:
                save_learn =[]
            else:
                exist_personbase = tem
                exist_IDcard = [item[0] for item in exist_personbase]
                exist_personbase = pd.DataFrame(exist_personbase).rename(columns={1: 'name_id', 0: 'IDcard'})
                save_learn = data[data['IDcard'].isin(exist_IDcard)]
                save_learn = pd.merge(exist_personbase, save_learn, on='IDcard').drop(columns=['name', 'IDcard']).to_dict('records')
            # 处理找不到毕业证编号
            exist_graduate = data[~data['graduate_number'].isin(graduate_numbers)]
            if exist_graduate.empty:
                save_graduate_learn = []
            else:
                exist_personbase = session.query(PersonBaseInfo.IDcard,PersonBaseInfo.id).filter(PersonBaseInfo.IDcard.in_(exist_graduate.IDcard.to_list())).all()
                exist_personbase = pd.DataFrame(exist_personbase).rename(columns={'id':'name_id'})
                save_graduate_learn = pd.merge(exist_personbase, exist_graduate, on='IDcard').drop(columns=['name', 'IDcard']).to_dict('records')
            # 处理毕业证编号为无
            data = data[data['graduate_number'].isin(['无',])]
            if data.empty:
                save_null_learn = []
            else:
                exist_personbase = session.query(PersonBaseInfo.IDcard, PersonBaseInfo.id).filter(
                    PersonBaseInfo.IDcard.in_(data.IDcard.to_list())).all()
                exist_personbase = pd.DataFrame(exist_personbase).rename(columns={'id': 'name_id'})
                save_null_learn = pd.merge(exist_personbase, data, on='IDcard').drop(
                    columns=['name', 'IDcard']).to_dict('records')
            # 写入数据库，以字典的形式存入
            save_learn = save_graduate_learn + save_learn + save_null_learn
            if not save_learn:
                QMessageBox.critical(None,'失败','导入共%s条，失败%s条'%(len(data),len(data)))
            else:
                learns = [LearningExperience(**item) for item in save_learn]
                session.add_all(learns)
                QMessageBox.information(None, '提示', '你导入成功%s条，失败%s条，明细如下！' % (len(save_learn), len(data) - len(save_learn)))
        self.loadTableData()
